package intro.databases.gtCarRental.util;

import intro.databases.gtCarRental.GTCRApplication.GTCR_MemberType;
import intro.databases.gtCarRental.classes.Car;
import intro.databases.gtCarRental.classes.Location;
import intro.databases.gtCarRental.classes.MaintenanceRequest;
import intro.databases.gtCarRental.classes.User;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;

public class DatabaseConnection {
	public Connection connection = null;

	public DatabaseConnection() {
		//		this.connect();
	}



	//**------------------------------------------------------------------------------------
	public void connect(){
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			connection = DriverManager.getConnection("jdbc:mysql://academic-mysql.cc.gatech.edu/cs4400_Group_25","cs4400_Group_25", "edAc9upL");
			System.out.println("Database connection established");
		} 
		catch(Exception e) {
			System.err.println("Exception: " + e.getMessage());
			e.printStackTrace();
		} 
	}	

	//**------------------------------------------------------------------------------------
	public void disconnect(){
		try {
			if(connection != null)
				connection.close();
		} catch(SQLException e) {}
	}





	//**------------------------------ EXPERIMENTAL CODE...-Kevin
	public ResultSet carSearch(String pickUpDate,String pickUpTime,String returnDate,String returnTime,	String location, String searchBy, String searchParam ){
		PreparedStatement pS;
		ResultSet rS = null;
		connect();
		int dayDif = dayDifference(pickUpDate,returnDate);
		int hourDif = hourDifference(pickUpTime,returnTime);
		try {
			pS = connection.prepareStatement("Select CarModel, Type, CarLocation, Color, " +
					"HourlyRate, DailyRate, Auxiliary_Cable, BluetoothConnectivity, " +
					"Transmission_Type,Seating_Capacity,Type, " +
					((dayDif > 0) ? (dayDif+"*DailyRate ") : (hourDif+"*HourlyRate ")) +
					"AS 'Estimated Cost', car.VehicleSno  " +
					"FROM car left join reservation ON car.VehicleSno = reservation.VehicleSno " +
					"where ((PickUpDateTime is null AND ReturnDateTime is null)" +
					" OR (? > reservation.ReturnDateTime OR ? < reservation.PickUpDateTime)) and "+searchBy+"=? " +
					"and CarLocation=?");
			//				if (dayDif > 0){
			//					pS.setString(1, dayDif + "");
			//					pS.setString(2, "DailyRate");
			//				}
			//				else if (dayDif ==0){
			//					pS.setString(1, hourDif + "");
			//					pS.setString(2, "HourlyRate");
			//				}
			pS.setString(1, pickUpDate);
			pS.setString(2,returnDate);
			//pS.setString(5,searchBy); // This will be "Type" or "CarModel"
			pS.setString(3,searchParam);
			pS.setString(4, location);
			rS = pS.executeQuery();


		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return rS;
	}

	// Determines number of days between two dates
	public int dayDifference(String date1, String date2){
		SimpleDateFormat ft = new SimpleDateFormat ("yyyy-MM-dd"); 
		java.util.Date d1;
		java.util.Date d2; 
		long dif = 0;

		try {
			d1 =  ft.parse(date1);
			d2 =  ft.parse(date2);
			dif = d2.getTime() - d1.getTime();
			dif = dif/1000/60/60/24;
		} catch (ParseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 

		return (int) dif;

	}

	public static int hourDifference(String time1, String time2){
		SimpleDateFormat ft = new SimpleDateFormat ("HH:mm"); 
		java.util.Date d1;
		java.util.Date d2; 
		long dif = 0;

		try {
			d1 =  ft.parse(time1);
			d2 =  ft.parse(time2);
			dif = d2.getTime() - d1.getTime();
			dif = dif/1000/60/60;
		} catch (ParseException e) {
			e.printStackTrace();
		} 

		return (int) dif;

	}
	//**---------------------------------------------------------------------
	//**-------------------------EXPERIMENTAL RENTAL CHANGE REQUEST CODE--KEVIN----
	public ResultSet rentalChangeUpdate(String username, String newArrivalDate, String 
			newArrivalTime){


		return null;



	}

	// Generates revenue for last 3 months.
	public ResultSet revenueGenerated(){
		PreparedStatement pS;
		ResultSet rS = null;
		connect();

		try {
			pS = connection.prepareStatement(
					"Select car.VehicleSno, Type, CarModel, SUM(EstimatedCost) as 'Reservation Revenue',SUM(LATEFEES) as 'Late Fee Revenue' " +
					"FROM car JOIN reservation ON car.VehicleSno = reservation.VehicleSno " +
					"WHERE DATEDIFF(PickUpDateTime, CURDATE()) > -90 AND DATEDIFF(PickUpDateTime, CURDATE()) < 90 " +
					"GROUP BY car.VehicleSno " +
					"ORDER BY Type ");

			rS = pS.executeQuery();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
//		disconnect();
		return rS;
	}

	// Generates location statistics for the last 3 months.
	public ResultSet locationPreference(){
		PreparedStatement pS;
		ResultSet rS = null;
		connect();

		try {
			pS = connection.prepareStatement("SELECT  month(PickUpDateTime) as Month, " +
					"monthname(PickUpDateTime) AS 'Month Name', ReservationLocation As 'Reservation Location', " +
					" Count(Distinct ReservationLocation) As 'Number of Reservations', SUM(DATEDIFF(ReturnDateTime,PickUpDateTime)*24) AS 'Total Hours' " +
					"FROM reservation " +
					"WHERE DATEDIFF(PickUpDateTime, CURDATE()) > -90 AND DATEDIFF(PickUpDateTime, CURDATE()) <0 " +
					"GROUP BY Month " +
					"ORDER BY `Month`  DESC");

			rS = pS.executeQuery();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

//		disconnect();
		return rS;
	}

	public ResultSet frequentUsers(){

		PreparedStatement pS;
		ResultSet rS = null;
		connect();

		try {
			pS = connection.prepareStatement("SELECT username, DrivingPlan, COUNT(Username)/3 AS 'Avg.Reservations'" +
					"FROM reservation NATURAL JOIN member " +
					"WHERE DATEDIFF(PickUpDateTime,CURDATE()) > -90 AND   DATEDIFF(PickUpDateTime,CURDATE()) < 0 " +
					"GROUP BY Username " +
					"ORDER BY COUNT(Username) DESC " +
					"LIMIT 0,5");
			rS = pS.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return rS;

	}

	public ResultSet MaintenanceHistoryReport(){

		PreparedStatement pS;
		ResultSet rS = null;
		connect();

		try {
			pS = connection.prepareStatement("SELECT CarModel, RequestDateTime, Username, Problem " +
					"FROM maintenance_request NATURAL JOIN " +
					"maintenance_request_problems " +
					"NATURAL JOIN car");
			rS = pS.executeQuery();
		} catch (SQLException e) {

			e.printStackTrace();
		}

		return rS;

	}
	
	
	public ResultSet currentReservations(String username){
		
		PreparedStatement pS;
		ResultSet rS = null;
		connect();
		
		try {
			pS = connection.prepareStatement("SELECT date(PickUpDateTime) As Date, time(PickUpDateTime) As 'Pick Up Time', " +
					"time(ReturnDateTime) As 'Return Time', CarModel, CarLocation, EstimatedCost " +
					"FROM reservation NATURAL JOIN car " +
					"WHERE reservation.Username = ? " +
					"AND DATEDIFF(ReturnDateTime,CURDATE()) > 0 ");
			pS.setString(1, username);
			rS = pS.executeQuery();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		disconnect();
		return rS;
	}
	
	public ResultSet previousReservations(String username){
		PreparedStatement pS;
		ResultSet rS = null;
		connect();
		
		try {
			pS = connection.prepareStatement("SELECT date(PickUpDateTime) As Date, time(PickUpDateTime) As 'Pick Up Time', time(ReturnDateTime) As 'Return Time', CarModel, CarLocation, EstimatedCost " +
					"FROM reservation NATURAL JOIN car " +
					"WHERE reservation.Username = ? " +
					"AND DATEDIFF(PickUpDateTime,CURDATE()) < 0");
			pS.setString(1, username);
			rS = pS.executeQuery();
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		
//		disconnect();
		return rS;
		
		
	}
	

	//**------------------------------------------------------------------------------------
	// Creates a user and inserts into the user table.
	public boolean createAccount( String userName, String password, GTCR_MemberType type, long creditCard){
		PreparedStatement pS;
		connect();
		try {
			//first insert into the USER table.
			pS = connection.prepareStatement("insert into user values (?,?)");
			pS.setString(1, userName);
			pS.setString(2, password);
			pS.executeUpdate();

			//Then check if a employee or regular member
			//May have to change the type check  method.
			switch(type) {
			//This case will never happen in-app
			//				case ADMIN:
			//					createAdmin(pS, userName);
			//					break;
			case EMPLOYEE:
				createEmployee(pS, userName);
				break;
			case MEMBER:
				createMember(pS, userName, creditCard);
				break;
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		disconnect();	

		return login(userName, password);
	}


	//**------------------------------------------------------------------------------------
	private void createEmployee(PreparedStatement pS, String userName) throws SQLException {
		pS = connection.prepareStatement("insert into GTCREmployee values (?)");
		pS.setString(1, userName);
		pS.executeUpdate();
	}

	//**------------------------------------------------------------------------------------
	private void createMember(PreparedStatement pS, String userName, long creditCard) throws SQLException {
		pS = connection.prepareStatement("insert into member values (?,?,?,?,?,?,?,?,?)");
		pS.setString(1, userName);
		pS.setString(2, ""); //fName);
		pS.setString(3, ""); //lName);
		pS.setString(4, ""); //middle);
		pS.setString(5, ""); //address);
		pS.setString(6, ""); //phone);
		pS.setString(7, ""); //email);
		pS.setLong(8, creditCard); //cardNo);
		pS.setString(9, ""); //drivingPlan);
		pS.executeUpdate();

		pS = connection.prepareStatement("insert into creditcard values (?,?,?,?,?)");
		pS.setLong(1, creditCard); //CardNo);
		pS.setInt(2, 0); //CVV);
		pS.setString(3, ""); //Name);
		pS.setDate(4, Date.valueOf("2014-01-01")); //ExpiryDate);
		pS.setString(5, ""); //BillingAdd);
		pS.executeUpdate();
	}

	//**------------------------------------------------------------------------------------
	//Searches DB for a user and pass combo and returns true if in DB and false if not.
	public boolean login( String userName, String password){
		//loginGUI gui = new loginGUI();
		PreparedStatement pS;
		ResultSet rS;
		connect();
		boolean ret = false;
		try {
			pS = connection.prepareStatement("SELECT user.username, user.password FROM user" +
					" WHERE user.Username = ? AND  user.Password = ?");
			pS.setString(1, userName);
			pS.setString(2, password);
			rS = pS.executeQuery();
			if (rS.next()) {
				//System.out.println("Logged in");
				ret=true;
			}
			else{
				//System.out.println("Did not find user");
				ret= false;
			}
		} catch (SQLException e) {	
			e.printStackTrace();
		}
		disconnect();
		return ret;
	}

	//**------------------------------------------------------------------------------------
	public User getUserFromCredentials(String username, String password) {
		User retVal = new User();
		PreparedStatement pS;
		ResultSet rS;
		connect();
		try {
			pS = connection.prepareStatement("SELECT user.username, user.password FROM user" +
					" WHERE user.Username = ? AND  user.Password = ?");
			pS.setString(1, username);
			pS.setString(2, password);
			rS = pS.executeQuery();
			if(rS.next()) {

				retVal.username = rS.getString(1);
				retVal.password = rS.getString(2);

				retVal.type = findUserTypeFromUserName(pS, rS, username);
				if(retVal.type == GTCR_MemberType.MEMBER) {
					pS = connection.prepareStatement("SELECT * FROM member WHERE Username = ?");
					pS.setString(1, username);
					rS = pS.executeQuery();
					if(rS.next()) {
						retVal.firstName = rS.getString(2);
						retVal.lastName = rS.getString(3);
						retVal.middleInit = rS.getString(4);
						retVal.address = rS.getString(5);
						retVal.phoneNo = rS.getString(6);
						retVal.email = rS.getString(7);
						retVal.creditCard = rS.getLong(8);
						retVal.drivingPlan = rS.getString(9);				
					}

					pS = connection.prepareStatement("SELECT * FROM creditcard WHERE CardNo = ?");
					pS.setLong(1, retVal.creditCard);
					rS = pS.executeQuery();
					if(rS.next()) {
						retVal.cvv = rS.getInt(2);
						retVal.nameOnCard = rS.getString(3);
						retVal.expiryDate = rS.getDate(4);
						retVal.billingAddress = rS.getString(5);
					}
				}
			} else {
				return null;
			}
		} catch (SQLException e) {	
			e.printStackTrace();
		}
		disconnect();
		return retVal;
	}

	//**------------------------------------------------------------------------------------
	private GTCR_MemberType findUserTypeFromUserName(PreparedStatement pS, ResultSet rS, String username) throws SQLException {
		GTCR_MemberType retVal = GTCR_MemberType.MEMBER;

		pS = connection.prepareStatement("SELECT * FROM member WHERE Username = ?");
		pS.setString(1, username);
		rS = pS.executeQuery();
		if(rS.next())
			retVal = GTCR_MemberType.MEMBER;

		pS = connection.prepareStatement("SELECT * FROM GTCREmployee WHERE Username = ?");
		pS.setString(1, username);
		rS = pS.executeQuery();
		if(rS.next())
			retVal = GTCR_MemberType.EMPLOYEE;

		pS = connection.prepareStatement("SELECT * FROM Administrator WHERE Username = ?");
		pS.setString(1, username);
		rS = pS.executeQuery();
		if(rS.next())
			retVal = GTCR_MemberType.ADMIN;

		return retVal;
	}

	//**------------------------------------------------------------------------------------
	public void updateUser(User user) {
		PreparedStatement pS;
		connect();
		try {
			if(user.type == GTCR_MemberType.MEMBER) {
				pS = connection.prepareStatement(
						"UPDATE member " +
								"SET FirstName=?, LastName=?, MiddleInit=?, Address=?, PhoneNo=?, EmailAddress=?, CardNo=?, DrivingPlan = ?" +
						"WHERE Username=?");
				pS.setString(1, user.firstName);
				pS.setString(2, user.lastName);
				pS.setString(3, user.middleInit);
				pS.setString(4, user.address);
				pS.setString(5, user.phoneNo);
				pS.setString(6, user.email);
				pS.setLong(7, user.creditCard);
				pS.setString(8, user.drivingPlan);
				pS.setString(9, user.username);
				pS.executeUpdate();

				pS = connection.prepareStatement(
						"UPDATE creditcard " +
								"SET CardNo=?, CVV=?,Name=?,ExpiryDate=?, BillingAdd=? " + 
						"WHERE CardNo=?");
				pS.setLong(1, user.creditCard);
				pS.setInt(2, user.cvv);
				pS.setString(3, user.nameOnCard);
				pS.setDate(4, user.expiryDate);
				pS.setString(5, user.billingAddress);
				pS.setLong(6, user.oCreditCard);
				pS.executeUpdate();
			}
		} catch (SQLException e) {	
			e.printStackTrace();
		}
		disconnect();
	}

	//**------------------------------------------------------------------------------------
	public ArrayList<Location> getAllLocations() {
		ArrayList<Location> retVal = new ArrayList<Location>();

		PreparedStatement pS;
		ResultSet rS;
		connect();
		try {
			pS = connection.prepareStatement("SELECT * FROM location");
			rS = pS.executeQuery();
			while(rS.next()) {
				Location loc = new Location();
				loc.locationName = rS.getString(1);
				loc.capacity = rS.getInt(2);
				retVal.add(loc);
			}
		} catch (SQLException e) {	
			e.printStackTrace();
		}
		disconnect();
		return retVal;
	}

	//**------------------------------------------------------------------------------------
	public Location getLocationFromName(String locationName) {
		Location retVal = new Location();

		PreparedStatement pS;
		ResultSet rS;
		connect();
		try {
			pS = connection.prepareStatement("SELECT * FROM location WHERE LocationName = ?");
			pS.setString(1, locationName);
			rS = pS.executeQuery();
			if(rS.next()) {
				retVal.locationName = rS.getString(1);
				retVal.capacity = rS.getInt(2);
			}
		} catch (SQLException e) {	
			e.printStackTrace();
		}
		disconnect();

		return retVal;
	}

	//**------------------------------------------------------------------------------------
	public ArrayList<Car> getAllCars() {
		ArrayList<Car> retVal = new ArrayList<Car>();

		PreparedStatement pS;
		ResultSet rS;
		connect();
		try {
			pS = connection.prepareStatement("SELECT * FROM car");
			rS = pS.executeQuery();
			while(rS.next()) {
				Car car = new Car();
				car.vehicleSNO = rS.getInt(1);
				car.auxillaryCable = rS.getInt(2) == 1;
				car.transmissionType = rS.getString(3);
				car.seatingCapacity = rS.getInt(4);
				car.bluetoothConnectivity = rS.getInt(5) == 1;
				car.dailyRate = rS.getInt(6);
				car.hourlyRate = rS.getInt(7);
				car.color = rS.getString(8);
				car.type = rS.getString(9);
				car.carModel = rS.getString(10);
				car.underMaintenanceFlag = rS.getInt(11) == 1;
				car.locationName = rS.getString(12);				
				retVal.add(car);
			}
		} catch (SQLException e) {	
			e.printStackTrace();
		}
		disconnect();
		return retVal;
	}

	//**------------------------------------------------------------------------------------
	public ArrayList<Car> getCarsByLocation(String locationName) {
		ArrayList<Car> retVal = new ArrayList<Car>();

		PreparedStatement pS;
		ResultSet rS;
		connect();
		try {
			pS = connection.prepareStatement("SELECT * FROM car WHERE CarLocation=?");
			pS.setString(1, locationName);
			rS = pS.executeQuery();
			while(rS.next()) {
				Car car = new Car();
				car.vehicleSNO = rS.getInt(1);
				car.auxillaryCable = rS.getInt(2) == 1;
				car.transmissionType = rS.getString(3);
				car.seatingCapacity = rS.getInt(4);
				car.bluetoothConnectivity = rS.getInt(5) == 1;
				car.dailyRate = rS.getInt(6);
				car.hourlyRate = rS.getInt(7);
				car.color = rS.getString(8);
				car.type = rS.getString(9);
				car.carModel = rS.getString(10);
				car.underMaintenanceFlag = rS.getInt(11) == 1;
				car.locationName = rS.getString(12);				
				retVal.add(car);
			}
		} catch (SQLException e) {	
			e.printStackTrace();
		}
		disconnect();
		return retVal;
	}

	//**------------------------------------------------------------------------------------
	public void insertCar(Car car) {
		PreparedStatement pS;
		connect();
		try {
			pS = connection.prepareStatement("INSERT INTO car VALUES (?,?,?,?,?,?,?,?,?,?,?,?)");
			pS.setInt(1, car.vehicleSNO);
			pS.setInt(2, (car.auxillaryCable) ? 1 : 0);
			pS.setString(3, car.transmissionType);
			pS.setInt(4, car.seatingCapacity);
			pS.setInt(5, (car.bluetoothConnectivity) ? 1 : 0);
			pS.setInt(6, car.dailyRate);
			pS.setInt(7, car.hourlyRate);
			pS.setString(8, car.color);
			pS.setString(9, car.type);
			pS.setString(10, car.carModel);
			pS.setInt(11, (car.underMaintenanceFlag) ? 1 : 0);
			pS.setString(12, car.locationName);
			pS.executeUpdate();
		} catch (SQLException e) {	
			e.printStackTrace();
		}
		disconnect();
	}

	//**------------------------------------------------------------------------------------
	public void updateCar(Car car) {
		PreparedStatement pS;
		connect();
		try {
			pS = connection.prepareStatement(
					"UPDATE car " +
							"SET VehicleSno=?, Auxiliary_Cable=?, Transmission_Type=?, Seating_Capacity=?, BluetoothConnectivity=?, DailyRate=?, HourlyRate=?, Color=?, Type=?, CarModel=?, UnderMaintenanceFlag=?, CarLocation=?" +
					"WHERE VehicleSno=?");
			pS.setInt(1, car.vehicleSNO);
			pS.setInt(2, (car.auxillaryCable) ? 1 : 0);
			pS.setString(3, car.transmissionType);
			pS.setInt(4, car.seatingCapacity);
			pS.setInt(5, (car.bluetoothConnectivity) ? 1 : 0);
			pS.setInt(6, car.dailyRate);
			pS.setInt(7, car.hourlyRate);
			pS.setString(8, car.color);
			pS.setString(9, car.type);
			pS.setString(10, car.carModel);
			pS.setInt(11, (car.underMaintenanceFlag) ? 1 : 0);
			pS.setString(12, car.locationName);
			pS.setInt(13, car.vehicleSNO);
			pS.executeUpdate();
		} catch (SQLException e) {	
			e.printStackTrace();
		}
		disconnect();
	}

	//**------------------------------------------------------------------------------------
	public MaintenanceRequest getMaintenanceRequestForCarSNO(int vehicleSNO, String currentUser) {
		MaintenanceRequest retVal = new MaintenanceRequest();

		PreparedStatement pS;
		ResultSet rS;
		connect();
		try {
			pS = connection.prepareStatement("SELECT * FROM maintenance_request_problems WHERE VehicleSno=?");
			pS.setInt(1, vehicleSNO);
			rS = pS.executeQuery();
			if(rS.next()) {
				retVal.vehicleSNO = rS.getInt(1);
				retVal.requestDateTime = rS.getDate(2);
				retVal.problems = rS.getString(3);
				retVal.username = currentUser;
			} else {
				pS = connection.prepareStatement("INSERT INTO maintenance_request VALUES (?,?,?)");
				pS.setInt(1, vehicleSNO);
				pS.setDate(2, new Date((new java.util.Date()).getTime()));
				pS.setString(3, currentUser);
				pS.executeUpdate();

				pS = connection.prepareStatement("INSERT INTO maintenance_request_problems VALUES (?,?,?)");
				pS.setInt(1, vehicleSNO);
				pS.setDate(2, new Date((new java.util.Date()).getTime()));
				pS.setString(3, "");
				pS.executeUpdate();
			}
		} catch (SQLException e) {	
			e.printStackTrace();
		}
		disconnect();
		return retVal;
	}

	//**------------------------------------------------------------------------------------
	public void updateMaintenanceRequestForCar(MaintenanceRequest request, Car car) {
		PreparedStatement pS;
		connect();
		try {
			pS = connection.prepareStatement(
					"UPDATE maintenance_request " +
							"SET VehicleSno=?, RequestDateTime=?, Username=? " +
					"WHERE VehicleSno=?");
			pS.setInt(1, request.vehicleSNO);
			pS.setDate(2, request.requestDateTime);
			pS.setString(3, request.username);
			pS.setInt(4, request.vehicleSNO);
			pS.executeUpdate();

			pS = connection.prepareStatement(
					"UPDATE maintenance_request_problems " +
							"SET VehicleSno=?, RequestDateTime=?, Problem=? " +
					"WHERE VehicleSno=?");
			pS.setInt(1, request.vehicleSNO);
			pS.setDate(2, request.requestDateTime);
			pS.setString(3, request.problems);
			pS.setInt(4, request.vehicleSNO);
			pS.executeUpdate();

			//			if(!request.problems.equals("")) {
			//				pS = connection.prepareStatement(
			//						"UPDATE car " +
			//						"SET UnderMaintenanceFlag=b ? " +
			//						"WHERE VehicleSno=?");
			//				pS.setInt(1, 1);
			//				pS.setInt(2, request.vehicleSNO);
			//				pS.executeUpdate();
			//			} else {
			//				pS = connection.prepareStatement(
			//						"UPDATE car " +
			//						"SET UnderMaintenanceFlag=b ? " +
			//						"WHERE car.VehicleSno=?");
			//				pS.setInt(1, 0);
			//				pS.setInt(2, request.vehicleSNO);
			//				pS.executeUpdate();
			//			}
			updateCar(car);
		} catch (SQLException e) {	
			e.printStackTrace();
		}
		disconnect();
	}



	public ResultSet carSearch(ArrayList<String> carSearchValues) {
		return carSearch(carSearchValues.get(0), carSearchValues.get(1), carSearchValues.get(2), carSearchValues.get(3), carSearchValues.get(4), carSearchValues.get(5), carSearchValues.get(6));
	}



	public void createReservation(String username, String pickupDateTime, String returnDateTime, String estCost, String location, String vehicleSNO) {
		PreparedStatement pS;
		connect();
		try {
			//first insert into the USER table.
			pS = connection.prepareStatement("INSERT INTO reservation VALUES (NULL,?,?,?,NULL,?,?, NULL,?,?) ");
			pS.setString(1, username);
			pS.setString(2, pickupDateTime);
			pS.setString(3, returnDateTime);
			pS.setString(4, "");
			pS.setString(5, estCost);
			pS.setString(6, location);
			pS.setString(7, vehicleSNO);
			pS.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		disconnect();	
	}

	//**------------------------------------------------------------------------------------
}
